package cn.itcast.json;

import database.jdbc.DBUtil;

import javax.json.Json;
import javax.json.JsonObject;
import javax.json.bind.Jsonb;
import javax.json.bind.JsonbBuilder;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.stream.Collectors;

@WebServlet("/json")
public class JsonServlet extends HttpServlet {
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, java.io.IOException {

        Jsonb jsonb = JsonbBuilder.create();
        String postJsonString = new BufferedReader(new InputStreamReader(req.getInputStream()))
                .lines().collect(Collectors.joining(System.lineSeparator()));
        postJsonString = new String(postJsonString.getBytes("gbk"),"utf-8");
        // 利用jsonb把json字符串转换为对象
        StudentDTO studentDTO = jsonb.fromJson(postJsonString, StudentDTO.class);
        String result = "";
//        PersonDTO personDTO = jsonb.fromJson("{\"姓名\":\"黎志雄\"}", PersonDTO.class);
        try (Connection conn = DriverManager.getConnection("jdbc:h2:~/test;MODE=MYSQL;DB_CLOSE_DELAY=-1", "sa", "sa")) {
            /*
                注意，Connection预设为自动“认可/提交”（Commit），也就是Statement执行SQL叙述完后，马上对数据库进行操作变更.
                如果想要对Statement要执行的SQL进行除错，可以使用setAutoCommit(false)来将自动认可取消，在执行完SQL之后，再呼叫Connection的commit()方法认可变更，
                使用Connection的getAutoCommit()可以测试是否设定为自动认可。
                不过无论是否有无执行commit()方法，只要SQL没有错，在关闭Statement或Connection前，都会执行认可commit动作，对数据库进行变更。
            */


            //3.通过数据库的连接操作数据库，实现增删改查
            Statement stmt = conn.createStatement();

            // 创建一个表
            // 如果表不存在就建立这个表，那么可以直接用 create table if not exists tablename 这样的指令来建立，不需要先去查询表是否存在。
            // 从模板表创建表：create table if not exists like old_table_name;
            stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS `user` (" +
                            "  `id` int(100) unsigned NOT NULL AUTO_INCREMENT," +
                            "  `stuNo` varchar(100) NOT NULL," +
                            "  `stuname` varchar(100) NOT NULL," +
                            "  `age` int(100) NOT NULL," +
                            "  `school` varchar(100) NOT NULL," +
                            "  `email` varchar(100) NOT NULL," +
                            "  PRIMARY KEY (`id`)" +
                            ");"
            );
            PreparedStatement preparedStatement = conn.prepareStatement("insert into `user` (stuNo,stuname,age,school,email) values (?, ?,?,?,?);");
            preparedStatement.setNString(1, studentDTO.getStuNo());
            preparedStatement.setNString(2, studentDTO.getStuName());
            preparedStatement.setInt(3, studentDTO.getAge());
            preparedStatement.setNString(4, studentDTO.getSchool());
            preparedStatement.setNString(5, studentDTO.getEmail());

            int a = preparedStatement.executeUpdate();
            if(a>0){
                ResultSet rs = stmt.executeQuery("select * from user");//选择import java.sql.ResultSet;

                // 遍历每行记录
                while (rs.next()) {
                    //如果结果集中有数据，就会循环打印出来
                    System.out.println(rs.getInt("id") + "," + rs.getString("stuNo") + "," + rs.getString("stuname")+"," +rs.getInt("age") + "," + rs.getString("school")+"," + rs.getString("email"));
                }
                result = "{\"code\":200,\"msg\":\"操作成功\"}";
            }else
            {
                result = "{\"code\":0,\"msg\":\"操作失败\"}";
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
//
//        // 把获取的字符串原样返回
//        String jsonPerson = jsonb.toJson(personDTO);
//
//        resp.setContentType("application/json");
//        resp.setCharacterEncoding("UTF-8");
//
//        resp.getWriter().println(jsonPerson);


        resp.getWriter().println(result);


    }

    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, java.io.IOException {

        // 创建一个简单的Person对象,用作演示
        //StudentDTO personDTO = new StudentDTO("66", "黎志雄", "12", "东莞理工学院","7536@qq.com");

        // 要获取对象的JSON表示，我们需要使用JsonbBuilder类及其toJson（）方法。
//        Jsonb jsonb = JsonbBuilder.create();
//        String jsonPerson = jsonb.toJson(personDTO);
//
//        resp.setContentType("application/json");
//        resp.setCharacterEncoding("UTF-8");
//
//        resp.getWriter().println(jsonPerson);
    }
}
